#C conc, 1.21 Si 1.89 Mn2.04 P0.12 S0.06 Cr4.24 Mo1.27 Ni4.38 Cu0.49 V0.55 Nb0.24 Al0.32
#C    k, 1.06 Si 1.04 Mn1.03 P1.06 S1.06 Cr0.99 Mo0.99 Ni1.01 Cu0.95 V1.04 Nb1.10 Al1.03
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
from PyQt5 import QtSql,QtCore
from datetime import date
import getpath
import os
import logging
import re
from openpyxl import load_workbook
from PIL import Image as PILImage
import config_json
import myglobal
import d4460
import copy
import numpy
import curvedata
def getSpecConcRSD(stdconc):
    if stdconc>1.0:
        return 0.005
    elif stdconc>0.1:
        return 0.01
    elif stdconc>0.01:
        return 0.02
    elif stdconc>0.001:
        return 0.05
    else:
        return 0.1
class MyDb():
    def __init__(self):
        self.init_db()
    def init_db(self):
        """
        init_db()
        Initializes the database.
        If tables "books" and "authors" are already in the database, do nothing.
        Return value: None or raises ValueError
        The error value is the QtSql error instance.
        """
        self.db = QSqlDatabase.addDatabase("QSQLITE")
        p = os.path.join(getpath.getpath(), "data.sqlite")
        self.db.setDatabaseName(p)
        self.q=QtSql.QSqlQuery()
        if not self.db.open():
            logging.debug("open failed")
            return False
        return True
    def getMethod(self):
        self.q.exec_("select methodid from method")
        r=[]
        while (self.q.next()):
            r.append(self.q.value(0))
        return r
    def saveCurrent(self):
        cmd = """update  setting set methodid=%d""" % (myglobal.methodid)
        self.sql_cmd(cmd)
    def deleteCurrentMethod(self):
        r=self.getMethod()
        if len(r)>1:#not delete last method
            cmd = """delete from std_result where methodid=%d""" % (myglobal.methodid)
            self.sql_cmd(cmd)
            cmd = """delete from method where methodid=%d""" % (myglobal.methodid)
            self.sql_cmd(cmd)
            cmd = """delete from curvedata where methodid=%d""" % (myglobal.methodid)
            self.sql_cmd(cmd)
            cmd = """delete from curvexishu where methodid=%d""" % (myglobal.methodid)
            self.sql_cmd(cmd)
            cmd = """delete from ele_curve where methodid=%d""" % (myglobal.methodid)
            self.sql_cmd(cmd)
            for one in r:
                if one!=myglobal.methodid:
                    myglobal.methodid=one
                    break
    def next_methodid(self):#,methodid,ele=):
        cmd = """select next_methodid from  setting"""
        self.q.exec_(cmd)
        if self.q.next():
            r=self.q.value(0)
            cmd = """update  setting set next_methodid=%d""" % (r+1)
            self.sql_cmd(cmd)
            return r+1
        return -1     
    def add_method(self):
        name="new method"
        mid=myglobal.methodid
        db=QtCore.QDateTime.currentDateTime()
        time = db.toString("yyyy-MM-dd hh:mm:ss")
        cmd = """insert into method(methodid,name,time) values(%d,'%s','%s')""" %(mid,name,time)
        self.q.exec_(cmd)
    def methodid(self):#,methodid,ele=):
        cmd = """select methodid from  setting"""
        self.q.exec_(cmd)
        if self.q.next():
            r=self.q.value(0)
            return r
        return -1  
    def curveid_tozero(self,methodid,ele,min_id):
        cmd = """update  curvedata  set curveid=0 where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,min_id)
        self.sql_cmd(cmd)
        cmd = """update  curvexishu  set curveid=0 where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,min_id)
        self.sql_cmd(cmd)
    def getAllCurveid(self,methodid,ele):
        cmd = """select curveid from  curvexishu where methodid=%d and ele='%s'""" %(methodid,ele)
        self.q.exec_(cmd)
        r=[]
        while self.q.next():
            r.append(self.q.value(0))
        return r
    def getCurveid(self,methodid,ele):
        cmd = """select curveid from  ele_curve where methodid=%d and ele='%s'""" %(methodid,ele)
        self.q.exec_(cmd)
        if self.q.next():
            r=self.q.value(0)
            return r
        return 0  
    def getCurve(self,methodid,ele,curveid=0):
        self.q.exec_("select * from curvedata where methodid=%d and ele='%s' and curveid=%d order by ele_measure" % (methodid,ele,curveid))
        columns=[]
        while (self.q.next()):
            x1= myglobal.myfloat(self.q.value(4))
            y1= myglobal.myfloat(self.q.value(3))
            y2= myglobal.myfloat(self.q.value(5))
            use= self.q.value(6)
            if use==1:
                columns.append([x1,y1,y2])
            else:
                pass
        print(columns) 
        filename=os.path.join(getpath.getpath(),"images",str(methodid)+"_"+ele+".jpg")
        img=PILImage.open(filename)    
        return (img)
    def getXishuCurrent(self,methodid,ele):
        curveid=self.getCurveid(methodid,ele)
        return self.getXishu(methodid,ele,curveid)
    def getXishuAll(self,methodid,ele):
        ids=self.getAllCurveid(methodid,ele)
        r=[]
        for curveid in ids:
            r.append(self.getXishu(methodid,ele,curveid))
        return r
    def getXishu(self,methodid,ele,curveid=0):
        cmd = """select x2,x1,x0,r,must00,ci from  curvexishu where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,curveid)
        self.q.exec_(cmd)
        if self.q.next():
            return [self.q.value(0),self.q.value(1),self.q.value(2),self.q.value(3),curveid,self.q.value(4),self.q.value(5)]
        return None
    def getMethodSampleids(self,methodid):
        cmd = """select sampleid from  std_result where methodid=%d """ % (methodid)
        self.q.exec_(cmd)
        r=[]
        while (self.q.next()):
            r.append(self.q.value(0))
        return r        

    def getMethodids(self,methodid):
        cmd = """select id from  Std_result where methodid=%d """ % (methodid)
        self.q.exec_(cmd)
        r=[]
        while (self.q.next()):
            r.append(self.q.value(0))
        return r    
    def getCurveData(self,methodid,ele,curveid=0):
        cmd = """select use,sampleid,ele_std,ele_measure,ele_calc from  curvedata where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,curveid)
        # sampleid    VARCHR,
        # ele_std     VARCHR,
        # ele_measure VARCHR,
        # ele_calc    DOUBLE,
        self.q.exec_(cmd)
        r=[]
        while (self.q.next()):
            if self.q.value(0)==1:
                r.append([self.q.value(1),self.q.value(2),self.q.value(3),self.q.value(4)])
        return r      
    def setCurveid(self,methodid,ele,curveid) :
        cmd = """select curveid from  ele_curve where methodid=%d and ele='%s'""" %(methodid,ele)
        self.q.exec_(cmd)
        if self.q.next():
            cmd="update ele_curve set curveid=%d where methodid=%d and ele='%s'" %(curveid,methodid,ele)
            myglobal.db.sql_cmd(cmd)
        else:
            cmd="insert into ele_curve(methodid,ele,curveid) values(%d,'%s',%d)" %(methodid,ele,curveid)
            myglobal.db.sql_cmd(cmd)
    def del_curve(self,methodid,ele,curveid=0):
        cmd = """delete from  curvexishu where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,curveid)
        self.q.exec_(cmd)
        cmd = """delete from  curvedata where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,curveid)
        self.q.exec_(cmd)
        ids=self.getAllCurveid(methodid,ele)
        if len(ids)>0:
            max_id=numpy.max(ids)
            self.setCurveid(methodid,ele,max_id)
        else:
            max_id=None
        return max_id
    def from_result(self):
        columns=self.table_info("result")
        fields=columns[5:]
        for f in fields:
            self.ensure_column("STD_Result",f)
        q2=QtSql.QSqlQuery()
        cmd = """select * from result"""
        q2.exec_(cmd)
        while (q2.next()):
            sampleid=q2.value(1)
            values=[]
            for i in range(len(columns)):
                if i>4:
                    values.append(q2.value(i))
            cmd="""insert into std_result(methodid,sampleid,%s) values(%d,'%s',%s)""" %(",".join(fields),myglobal.methodid,sampleid,",".join(["'"+str(x)+"'" for x in values]))
            print(cmd)
            self.sql_cmd(cmd)
            # rows.append([self.q.value(0),self.q.value(1),self.q.value(2)])
    def std_result_to_curvedata(self,methodid,ele,curveid=0):
        # select std_result.sampleid,std.Si_ as ele_std,std_result.Si_ as ele_measure,"Si" as ele from std,std_result where std.sampleid=std_result.sampleid into tmptable
        cmd = """delet from curvedata where methodid=%d and ele='%s' and curveid=%d""" % (methodid,ele,curveid)
        self.q.exec_(cmd)
        logging.info(ele)
        cmd = """select std_result.sampleid,std.%s_ ,std_result.%s_  from std,std_result where std.sampleid=std_result.sampleid and std_result.methodid=%d""" % (ele,ele,methodid)
        self.q.exec_(cmd)
        rows=[]
        while (self.q.next()):
            rows.append([self.q.value(0),self.q.value(1),self.q.value(2)])
        print(rows)
        #self.q.finish()
        for row in rows:
            cmd = """insert into curvedata(methodid,ele,sampleid,ele_std,ele_measure,use,curveid) values(%d,'%s','%s','%s','%s',1,%d)""" % (methodid,ele,row[0],row[1],row[2],curveid)
            self.q.exec_(cmd)
        #self.q.finish()
    def create_table_curvedata(self):
        tablename="curvedata"
        cmd = """CREATE TABLE """+tablename+""" (
id       INTEGER  PRIMARY KEY AUTOINCREMENT
                    NOT NULL,
sampleid varchr,
ele  varchr,
ele_std    varchr,
ele_measure   varchr,
use bool
)""" 
        logging.info(cmd)
        q = QSqlQuery()
        q.prepare(cmd)
        r = q.exec_()
        if not r:
            e = q.lastError()
            logging.info(e)
        q.finish()        
    def save_xishu(self,methodid,ele,x2,x1,x0,r,curveid,must00,ci): 
        has=False
        self.q.exec_("select * from curvexishu where methodid=%d and ele='%s' and curveid=%d" % (methodid,ele,curveid))
        if self.q.next():
            has=True
        self.q.finish()
        if has:
            cmd="update curvexishu set x2=%f,x1=%f,x0=%f,r=%f,must00=%d,ci=%d where methodid=%d and ele='%s' and curveid=%d" %(x2,x1,x0,r,must00,ci,methodid,ele,curveid)
        else:
            cmd="insert into curvexishu(methodid,ele,x2,x1,x0,r,curveid,must00,ci) values(%d,'%s',%f,%f,%f,%f,%d,%d,%d)" %(methodid,ele,x2,x1,x0,r,curveid,must00,ci)
        self.sql_cmd(cmd)
    def table_info(self,tablename):
        self.q.exec_("pragma table_info(%s)" % tablename)
        columns=[]
        while (self.q.next()):
            # rec = self.q.record()
            columns.append(self.q.value(1))
        # print(columns)
        return columns
    def get_std_ids(self):
        self.q.exec_("select sampleid from std")
        columns=[]
        while (self.q.next()):
            # rec = self.q.record()
            columns.append(str(self.q.value(0)))
        logging.info(columns)
        return columns
    def dropTable(self,tablename):
        cmd = """drop TABLE %s""" %tablename
        self.sql_cmd(cmd)
    def sql_cmd(self,cmd):
        self.q.prepare(cmd)
        logging.info(cmd)
        r = self.q.exec_()
        self.q.finish()
        if not r:
            logging.info(self.q.lastError())
    def elementWave(self, s):
        if s==None:
            return("","")
        wave = re.search(r"\d+(\.\d+)?", s)
        if wave!=None:
            wave=wave.group()
            element = s.split(" ")[0]
        else:
            wave=""
            element=s
        return (element, wave)            
    def test(self):
        cmd="""insert into STDS (
    sampleid,mass,volume,xishi,'C_','Si','Mn_','P_','S_','Cr_','Mo_','Ni_','Cu_','V_','Nb_','W_','Co_','Ti_','Al_','B_','N_','As_','Sn_','Pb_','Sb_','Zr_','Ca_','Ta_','Mg_') values('BS M-50','0','0','','0.834','0.205','0.244','0.0066','0.0009','4.28','4.29','0.074','0.064','0.99','0.0008','0.0052','0.0151','0.0018','0.073','0.0001','0.0057','0.0035','None','None','0.0006','0.0006','None','None','0.0002')"""
        self.sql_cmd(cmd)
    def import_std_file(self, fname):
        self.dropTable("STD")
        xlBook = load_workbook(filename=fname)
        table = xlBook.worksheets[0]
        logging.info(fname)
        create_table = False
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
                else:
                    r.append(None)
            data.append(r)
        logging.info(data)
        create_table = False
        samples = []
        at = 1
        # print(data)
        while True:
            sample = {"items": [], "conc": []}
            sample["mass"] = "0"
            sample["volume"] = "0"
            sample["items"] = data[0][1:]
            for i in range(len(sample["items"])):
                (ele, wave) = self.elementWave(str(sample["items"][i]).strip())
                sample["items"][i] = (ele, wave)
            try:
                sampleid = data[at][0]
                if sampleid == "" or sampleid==None:
                    break
                sample["sampleid"] = str(sampleid)
                sample["conc"] = []
                for one in data[at][1:]:
                    sample["conc"].append(one)  # remove %
                logging.info(sampleid)
            except IndexError:
                break
            if not create_table:
                self.cTable(sample,"STD")
                create_table = True
            self.iTable(sample,"STD")
            # samples.append(sample)
            at += 1
        # logging.info(samples)
        # logging.info(len(samples)) 
    def import_std_file2(self, fname):
        xlBook = load_workbook(filename=fname)
        table = xlBook.worksheets[0]
        logging.info(fname)
        data = []
        xlBook = load_workbook(filename=fname)
        for table in  xlBook.worksheets:
            self.import_std_file2_sheet(table)
    def import_std_file2_sheet(self, table):
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
                else:
                    r.append(None)
            data.append(r)
        logging.info(data)
        samples = []
        at = 1
        while True:
            sample = {"items": [], "conc": []}
            sample["mass"] = "0"
            sample["volume"] = "0"
            for one in data[0][1:]:
                if one==None or one.strip()=="":
                    break
                else:
                    sample["items"].append(one)
            n_items=len(sample["items"])
            # sample["items"] =[one for one in sample["items"]]# data[0][1:]
            for i in range(len(sample["items"])):
                (ele, wave) = self.elementWave(str(sample["items"][i]).strip())
                sample["items"][i] = (ele, wave)
            try:
                sampleid = data[at][0]
                if sampleid == "" or sampleid==None:
                    break
                sample["sampleid"] = str(sampleid)
                sample["conc"] = []
                for one in data[at][1:]:
                    sample["conc"].append(one)  # remove %
                    if len(sample["conc"])==n_items:
                        break
                logging.info(sampleid)
            except IndexError:
                break
            self.ensure_columns(sample,"STD")
            self.iTable2(sample,"STD")
            at += 1
    def import_std_result_file(self, fname):
        self.dropTable("STD_Result")
        xlBook = load_workbook(filename=fname)
        table = xlBook.worksheets[0]
        logging.info(dir(table))
        create_table = False
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
                else:
                    r.append(None)
            data.append(r)
        data=data[2:]
        create_table = False
        samples = []
        at = 1
        # print(data)
        while True:
            sample = {"items": [], "conc": []}
            sample["mass"] = "0"
            sample["volume"] = "0"
            sample["items"] = data[0][1:]
            for i in range(len(sample["items"])):
                (ele, wave) = self.elementWave(sample["items"][i])
                sample["items"][i] = (ele, wave)
            try:
                sampleid = data[at][0]
                sample["sampleid"] = str(sampleid)
                if sampleid == "Rep":
                    break
                sample["conc"] = []
                for one in data[at][1:]:
                    sample["conc"].append(one)  # remove %
                logging.info(sampleid)
            except IndexError:
                break
            if not create_table:
                self.cTable(sample,"STD_Result")
                create_table = True
            self.iTable(sample,"STD_Result")
            samples.append(sample)
            at += 1
        # logging.info(samples)
        logging.info(len(samples))  

    def import_std_result_file2(self, fname):        
        if config_json.setting["sblx"]=="spec":
            self.import_std_result_file2_spec(fname)
        else:
            self.import_std_result_file2_4460(fname)
    def import_std_result_file2_4460(self, fname):
        data=d4460.getdata_std(fname)
        create_table = False
        samples = []
        at = 1
        sample0 = {"items": [], "conc": []}
        sample0["mass"] = "0"
        sample0["volume"] = "0"
        sample0["items"] = data[0][1:]
        for i in range(len(sample0["items"])):
            (ele, wave) = self.elementWave(sample0["items"][i])
            sample0["items"][i] = (ele, wave)
        self.ensure_columns(sample0,"STD_Result")
        while True:
            sample = copy.copy(sample0)
            try:
                sampleid = data[at][0]
                sample["sampleid"] = str(sampleid)
                if sampleid == "Rep":
                    break
                sample["conc"] = []
                for one in data[at][1:]:
                    sample["conc"].append(one)  # remove %
                logging.info(sampleid)
            except IndexError:
                break
            # if not create_table:
            #     self.cTable(sample,"STD_Result")
            #     create_table = True
            self.iTable_std_result(sample,"STD_Result")
            samples.append(sample)
            at += 1
        # logging.info(samples)
        logging.info(len(samples))                   

    def import_std_result_file2_spec(self, fname):
        # self.dropTable("STD_Result")
        xlBook = load_workbook(filename=fname)
        table = xlBook.worksheets[0]
        logging.info(dir(table))
        create_table = False
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
                else:
                    r.append(None)
            data.append(r)
        data=data[2:]
        create_table = False
        samples = []
        at = 1
        # print(data)
        while True:
            sample = {"items": [], "conc": []}
            sample["mass"] = "0"
            sample["volume"] = "0"
            sample["items"] = data[0][1:]
            for i in range(len(sample["items"])):
                (ele, wave) = self.elementWave(sample["items"][i])
                sample["items"][i] = (ele, wave)
            try:
                sampleid = data[at][0]
                sample["sampleid"] = str(sampleid)
                if sampleid == "Rep":
                    break
                sample["conc"] = []
                for one in data[at][1:]:
                    sample["conc"].append(one)  # remove %
                logging.info(sampleid)
            except IndexError:
                break
            # if not create_table:
            #     self.cTable(sample,"STD_Result")
            #     create_table = True
            self.ensure_columns(sample,"STD_Result")
            self.iTable_std_result(sample,"STD_Result")
            samples.append(sample)
            at += 1
        # logging.info(samples)
        logging.info(len(samples))                   
    def cTable(self, sample,tablename="Result"):
        self.fields = [{"ele": None, "name": "id", "title": "id", "visible": False
                        }, {"ele": None, "name": "sampleid", "title": "样品编号", "visible": True
                            }, {"ele": None, "name": "beizhu", "title": "备注", "visible": True
                                }, {"ele": None, "name": "mass", "title": "质量", "visible": True
                                    }, {"ele": None, "name": "volume", "title": "体积", "visible": True
                                        }, {"ele": None, "name": "xishi", "title": "稀释倍数", "visible": True
                                            }]
        self.eleColumns = len(self.fields)
        for item in sample["items"]:
            logging.info(item)
            title = item[0]+" "+item[1]
            # to valid column name
            name = item[0]+"_"+item[1].replace(".", "Q")
            self.fields.append(
                {"ele": item[0], "wave": item[1], "name": name, "title": title, "visible": True})
        # if config_json.setting["sblx"] in ["ajl_csv","icap"]:
        #     self.sortFields()
        cmd = """CREATE TABLE """+tablename+""" (
id       INTEGER  PRIMARY KEY AUTOINCREMENT
                    NOT NULL,
sampleid varchr,
beizhu  varchr,
mass    varchr,
volume   varchr,
xishi varchr,
%s)""" % self.genSqlArrDeclare_new()
        logging.info(cmd)
        q = QSqlQuery()
        q.prepare(cmd)
        r = q.exec_()
        if not r:
            e = q.lastError()
            logging.info(e)
        q.finish()
        self.sqlArrName = self.gensqlArrName()
    def dropTable(self,tablename):
        cmd = """drop TABLE %s""" %tablename
        logging.info(cmd)
        self.q.prepare(cmd)
        r = self.q.exec_()
        if not r:
            e = self.q.lastError()
            logging.info(e.text())
        self.q.finish()        
    def ensure_columns(self,sample,tablename):
        already=self.table_info(tablename)
        new=[]
        for item in sample["items"]:
            logging.info(item)
            name = item[0]+"_"+item[1].replace(".", "Q")
            if name in already:
                pass
            else:
                # new.append(name)
                cmd="alter table %s add column %s varchar" % (tablename,name)
                self.sql_cmd(cmd)
    def ensure_column(self,tablename,column_name):
        already=self.table_info(tablename)
        if column_name in already:
            pass
        else:
            cmd="alter table %s add column %s varchar" % (tablename,column_name)
            self.sql_cmd(cmd)                
    def iTable2(self, sample, tablename="STD"):
        logging.info(sample)
        q = QSqlQuery()
        self.sqlArrName=self.gensqlArrName_sample(sample)
        cmd = """insert into """+tablename+""" (
sampleid,%s) values(%s,%s)""" % (
            self.sqlArrName, "'" +
            sample["sampleid"] + "'", self.genSqlArr(sample["conc"]))
        logging.info(cmd)
        q.prepare(cmd)
        r = q.exec_()
        if not r:
            e = q.lastError()
            logging.info(e)
            logging.info(dir(e))
        q.finish()

    def iTable_std_result(self, sample, tablename="STD_result"):
        logging.info(sample)
        q = QSqlQuery()
        xishi=sample.get("xishi","")
        self.sqlArrName=self.gensqlArrName_sample(sample)
        cmd = """insert into """+tablename+""" (
sampleid,%s,methodid) values(%s,%s,%d)""" % (
            self.sqlArrName, "'" +
            sample["sampleid"] + "'", self.genSqlArr(sample["conc"]),myglobal.methodid)
        logging.info(cmd)
        q.prepare(cmd)
        r = q.exec_()
        if not r:
            e = q.lastError()
            logging.info(e)
            logging.info(dir(e))
        q.finish()
    def iTable(self, sample,tablename="Result"):
        logging.info(sample)
        q = QSqlQuery()
        xishi=sample.get("xishi","")
        cmd = """insert into """+tablename+""" (
sampleid,mass,volume,xishi,%s) values(%s,%s,%s,%s,%s)""" % (
            self.sqlArrName, "'" +
            sample["sampleid"] + "'", "'" + sample["mass"] + "'",
            "'" + str(sample["volume"]) + "'","'" + str(xishi)+ "'", self.genSqlArr(sample["conc"]))
        logging.info(cmd)
        q.prepare(cmd)
        r = q.exec_()
        if not r:
            e = q.lastError()
            logging.info(e)
            logging.info(dir(e))
        q.finish()

    def genSqlArrDeclare_new(self):
        r = []
        for one in self.fields:
            if one.get("ele") is None:
                pass
            else:
                r.append(one["name"] + " varchr")
        return ",".join(r)
    def gensqlArrName_sample(self,sample):
        r=[]
        for item in sample["items"]:
            name = item[0]+"_"+item[1].replace(".", "Q")
            r.append(name)
        return ",".join(r)
    def gensqlArrName(self):
        r = []
        for one in self.fields:
            if one.get("ele") is None:
                pass
            else:
                r.append("'" + one["name"] + "'")
        return ",".join(r)

    def genSqlArr(self, arr):
        r = []
        for one in arr:
            r.append("'" + str(one) + "'")
        return ",".join(r)
    def add_use(self):
        self.sql_cmd("""alter table curvedata add column use bool""")
    def test_update(self):
        cmd="create table tmp(id integer,v integer)"
        self.sql_cmd(cmd)
    def test_findSTD(self,methodid):    
        columns=self.table_info("STD_result")
        cmd = """select * from STD_result where methodid=%d""" % methodid
        self.q.exec_(cmd)
        ct=0
        while self.q.next():
            r=[]
            for i in range(len(columns)):
                r.append(self.q.value(i))
            # print(r)        
            # print(columns)
            res=[]
            for i in range(len(columns)):
                if i>2:
                    # print(columns[i],r[i])
                    ele=columns[i][:-1]
                    v=myglobal.myfloat(r[i])
                    rsd=getSpecConcRSD(v)
                    k=3.0
                    up=v*(1.0+k*rsd)
                    low=v*(1.0-k*rsd)
                    cmd="""select sampleid,CAST(%s_ as decimal) as %s from STD where %s>%f and %s<%f""" % (ele,ele,ele,low,ele,up)
                    q2=QtSql.QSqlQuery()
                    q2.exec_(cmd)
                    r_ele=[]
                    while q2.next():
                        # print(self.q.value(0),self.q.value(1))
                        r_ele.append(q2.value(0))
                    if len(r_ele)!=0:
                        res.append((ele,v,low,up,r_ele))    
            res.sort(key=lambda x:x[1],reverse=True)
            print("----------",r[2],res[:3])
            # ct+=1
            # if ct==5:
            #     break
    def noneToblank(self):
        columns=self.table_info("STD")
        for column in columns:
            cmd=f"""update std set {column}=null where {column}="" """
            print(cmd)
            self.sql_cmd(cmd)
    def importCurveData(self,filename):
        data=curvedata.getdata(filename)
        print(data)
if __name__=="__main__":
    db=MyDb()
    # db.add_use()
    # db.table_info("std")
    file=os.path.join(getpath.getpath(),"stds.xlsx")
    # # file=os.path.join(getpath.getpath(),"spec","不锈钢Ni3-6，8-12.xlsx")
    # db.import_std_file2(file)
    # df_excel = pd.read_excel(file)
    # db.test_findSTD(8)
    # db.noneToblank()
    db.importCurveData(file)